-- PeM BEGIN - přesun ATR do nadřízené třídy (SPUSTIT)
<g>
-- not support system attributes (exception is missing)
create procedure dbo.ChangeAttrClass(@AttrIC bigint, @AttrU int, @OClassIC bigint, @OClassU int, @NClassIC bigint, @NClassU int) as
begin
  if exists(select * from a103u0 where (ic_a = @OClassIC) and (u_a = @OClassU) and (ic_b = @AttrIC) and (u_b = @AttrU))
  begin
	declare @attrname nvarchar(50)
	set @attrname = 'a' + convert(nvarchar(30),@AttrIC) + 'u' + convert(nvarchar(20),@AttrU)
	declare @oclassname nvarchar(50)
	set @oclassname = 'a' + convert(nvarchar(30),@OClassIC) + 'u' + convert(nvarchar(20),@OClassU)
	declare @nclassname nvarchar(50)
	set @nclassname = 'a' + convert(nvarchar(30),@NClassIC) + 'u' + convert(nvarchar(20),@NClassU)

	declare @attrtype smallint
	declare @attrprec smallint
	declare @attrcollation nvarchar(50)
	declare @islangattr bit
	declare @langsuffix nvarchar(100)
	declare @fullattrname nvarchar(100)
	declare @attrtypename nvarchar(100)

	select @islangattr = case when a222u0 in (2,9,13) then 1 else 0 end from a102u0 where ic = @AttrIC and u = @AttrU

	if (@islangattr = 1)
	begin
	    declare langs scroll cursor for select 'l' + convert(nvarchar(30),ic) + 'u' + convert(nvarchar(20),u) from a194u0
		open langs
		fetch first from langs into @langsuffix
		while (@@fetch_status = 0)
		begin
		  set @fullattrname = @attrname + @langsuffix

		  select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @fullattrname		  
		  if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		  if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		  if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		  if (@attrtype = 52) set @attrtypename = 'smallint' else
		  if (@attrtype = 56) set @attrtypename = 'integer' else
		  if (@attrtype = 127) set @attrtypename = 'bigint' else
		  if (@attrtype = 62) set @attrtypename = 'float' else
		  if (@attrtype = 61) set @attrtypename = 'datetime' else
		  if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename)
		exec('update a set ' + @fullattrname + ' = b.' + @fullattrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @fullattrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @fullattrname)
		  exec('create index i_' + @fullattrname + ' on ' + @nclassname + '(' + @fullattrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @fullattrname)  
		  fetch next from langs into @langsuffix
        end
        close langs
        deallocate langs
	end
	else
	begin	
		select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @attrname
		
		if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		if (@attrtype = 52) set @attrtypename = 'smallint' else
		if (@attrtype = 56) set @attrtypename = 'integer' else
		if (@attrtype = 127) set @attrtypename = 'bigint' else
		if (@attrtype = 62) set @attrtypename = 'float' else
		if (@attrtype = 61) set @attrtypename = 'datetime' else
		if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename)
		exec('update a set ' + @attrname + ' = b.' + @attrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @attrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @attrname)
		  exec('create index i_' + @attrname + ' on ' + @nclassname + '(' + @attrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @attrname)  
    end

	update a103u0 set ic_a = @NClassIC, u_a = @NClassU where ic_b = @AttrIC and u_b = @AttrU
  end
end
</g>

<g>
-- pouziti (SPUSTIT)
exec ChangeAttrClass 209218,9,209217,9,42107567,10;
exec ChangeAttrClass 209220,9,209217,9,42107567,10;
exec ChangeAttrClass 23276720,10,209217,9,42107567,10;
exec ChangeAttrClass 40582831,10,209217,9,42107567,10;
exec ChangeAttrClass 40615039,10,209217,9,42107567,10;
exec ChangeAttrClass 40705909,10,209217,9,42107567,10;
exec ChangeAttrClass 40708793,10,209217,9,42107567,10;
</g>

<g>
-- výmaz proc (SPUSTIT)
drop procedure ChangeAttrClass
</g>
-- PeM-END

<g>
-- Run ; change lowercase for login
update a102u0 set a226u1 = null where ic = 272 and u = 0
</g>

-- JN BEGIN (SPUSTIT)
<g>
-- definice importů
<p>
insert into a179303u10 (ic,u,ts0,langstate,useric,useru)
  select a.ic,a.u,a.ts0,a.langstate,a.useric,a.useru from a12519988u10 as a left outer join a179303u10 as b on a.ic = b.ic and a.u = b.u where b.ic is null
</p>
<a>
insert into a179303u10 (ic,u,ts0,langstate,useric,useru,sstate)
  select a.ic,a.u,a.ts0,a.langstate,a.useric,a.useru,a.sstate from a12519988u10 as a left outer join a179303u10 as b on a.ic = b.ic and a.u = b.u where b.ic is null
</a>
</g>  
-- JN END


-- Dingo (NESPOUŠTĚT)
-- Doprava: Objekty casti zasilky -> Nadrizene manipulacni jednotky
<g>
	<p>
	insert into a30626439u10 (u, ic, ts0, useric, useru, remotekey, langstate, u_a, ic_a, u_b, ic_b)
	select src.u, src.ic, getdate(), 1105, 0, src.remotekey, src.langstate, src.u_a, src.ic_a, src.u_b, src.ic_b
	</p>
	<a>
	insert into a30626439u10 (u, ic, ts0, useric, useru, remotekey, langstate, u_a, ic_a, u_b, ic_b, sstate, sstate_a, sstate_b)
	select src.u, src.ic, getdate(), 1105, 0, src.remotekey, src.langstate, src.u_a, src.ic_a, src.u_b, src.ic_b, 0, 0, 0
	</a>
	from
	  a813089u10212 src
	  left outer join a30626439u10 dst on src.ic = dst.ic and src.u = dst.u
	where
	  dst.ic is null
</g>


-- Dingo END



-- KaSo BEGIN - Nastavení atributu "Automatické vkládání do dokladů" (1176461,10) ve třídě "Obaly" (626383,10) na NULL, když je množina prázdná tj. hodnota 0 - []. (NESPUSTIT)
<g>
UPDATE Obaly SET a1176461u10 = NULL FROM a626383u10 AS Obaly WHERE a1176461u10 = 0
</g>
-- KaSo END

-- MiK Vychozi sada stylu skinu (SPOUSTET)
<g>
Update dbo.A134U0 set TS0 = GetDate(), UserIC = 1105, UserU = 0, A258U0 = 'procedure(CData: TCData);
var
  Fil: string;
  DS: TAppDataSet;
  I: Integer;
  SkinStyleSetID, StyleTypeID, StyleSetID: string;
begin
  if ((CData.Action = mtaInsert) or (CData.Action = mtaUpdate)) and (CData.NewValue[''553,1''] = ''1'') then begin
    StyleSetID := Format(''%%,%%'', [CData.NewValue[''ic_a''], CData.NewValue[''u_a'']]);
    StyleTypeID := AppServer.GetDBKeyValue(SourceName, rmPrimary, ''189,1'', StyleSetID, ftIDA, ftIDB);
    if StyleTypeID <> '''' then begin
      DS := TAppDataSet.Create;
      try
        DS.SetDSParams(SourceName, rmPrimary, ''40846009,10'', dsaReadNonVisible);
        Fil := Format(''("ic" <>  %% or "u" <> %%) and ("ic_b" = %% and "u_b" = %%) and ("40846012,10.ic" = %% and "40846012,10.u" = %%)'', [CData.NewValue[''ic''], CData.NewValue[''u''], CData.NewValue[''ic_b''], CData.NewValue[''u_b''], LByID(StyleTypeID), RByID(StyleTypeID)]);       
        DS.Filter := Fil;
        DS.Open;
        while not (DS.EOF) do begin
          SkinStyleSetID := DS.GetKeyValue('''', ftID);
          AppServer.SetDBAtrValue(SourceName, '''', ''190,1'', ''553,1'', SkinStyleSetID, ''0'');        
          DS.Next;        
        end;
        DS.Close;      
      finally
        DS.Free;
      end;    
    end;
  end;
end.
' where (ic = 40745828) and (u = 10)
</g>

-- JN verze (NESPUSTIT)
-- transfer all attributes from class 180,0 to 12049999,10
<g>
create procedure dbo.ChangeAttrClass(@AttrIC bigint, @AttrU int, @OClassIC bigint, @OClassU int, @NClassIC bigint, @NClassU int) as
begin
  if exists(select * from a103u0 where (ic_a = @OClassIC) and (u_a = @OClassU) and (ic_b = @AttrIC) and (u_b = @AttrU))
  begin
	declare @attrname nvarchar(50)
	set @attrname = 'a' + convert(nvarchar(30),@AttrIC) + 'u' + convert(nvarchar(20),@AttrU)
	declare @oclassname nvarchar(50)
	set @oclassname = 'a' + convert(nvarchar(30),@OClassIC) + 'u' + convert(nvarchar(20),@OClassU)
	declare @nclassname nvarchar(50)
	set @nclassname = 'a' + convert(nvarchar(30),@NClassIC) + 'u' + convert(nvarchar(20),@NClassU)

	declare @attrtype smallint
	declare @attrprec smallint
	declare @attrcollation nvarchar(50)
	declare @islangattr bit
	declare @langsuffix nvarchar(100)
	declare @fullattrname nvarchar(100)
	declare @attrtypename nvarchar(100)

	select @islangattr = case when a222u0 in (2,9,13) then 1 else 0 end from a102u0 where ic = @AttrIC and u = @AttrU

	if (@islangattr = 1)
	begin
	    declare langs scroll cursor for select 'l' + convert(nvarchar(30),ic) + 'u' + convert(nvarchar(20),u) from a194u0
		open langs
		fetch first from langs into @langsuffix
		while (@@fetch_status = 0)
		begin
		  set @fullattrname = @attrname + @langsuffix

		  select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @fullattrname		  
		  if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		  if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		  if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		  if (@attrtype = 52) set @attrtypename = 'smallint' else
		  if (@attrtype = 56) set @attrtypename = 'integer' else
		  if (@attrtype = 127) set @attrtypename = 'bigint' else
		  if (@attrtype = 62) set @attrtypename = 'float' else
		  if (@attrtype = 61) set @attrtypename = 'datetime' else
		  if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename)
		exec('update a set ' + @fullattrname + ' = b.' + @fullattrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @fullattrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @fullattrname)
		  exec('create index i_' + @fullattrname + ' on ' + @nclassname + '(' + @fullattrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @fullattrname)  
		  fetch next from langs into @langsuffix
        end
        close langs
        deallocate langs
	end
	else
	begin	
		select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @attrname
		
		if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		if (@attrtype = 52) set @attrtypename = 'smallint' else
		if (@attrtype = 56) set @attrtypename = 'integer' else
		if (@attrtype = 127) set @attrtypename = 'bigint' else
		if (@attrtype = 62) set @attrtypename = 'float' else
		if (@attrtype = 61) set @attrtypename = 'datetime' else
		if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename)
		exec('update a set ' + @attrname + ' = b.' + @attrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @attrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @attrname)
		  exec('create index i_' + @attrname + ' on ' + @nclassname + '(' + @attrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @attrname)  
    end

	update a103u0 set ic_a = @NClassIC, u_a = @NClassU where ic_b = @AttrIC and u_b = @AttrU
  end
end
</g>

<g>
if (exists(select * from a103u0 where ic_a = 180 and u_a = 0 and ic_b = 362560))
begin
  exec ChangeAttrClass 362560,10,180,0,12049999,10;
  exec ChangeAttrClass 106461,10,180,0,12049999,10;
  exec ChangeAttrClass 5570716,10,180,0,12049999,10;
  exec ChangeAttrClass 393790,10,180,0,12049999,10;
  exec ChangeAttrClass 393830,10,180,0,12049999,10;
  exec ChangeAttrClass 1014194,10,180,0,12049999,10;
  exec ChangeAttrClass 276,0,180,0,12049999,10;
  exec ChangeAttrClass 826360,10,180,0,12049999,10;
  exec ChangeAttrClass 3726284,10,180,0,12049999,10;
  exec ChangeAttrClass 3726286,10,180,0,12049999,10;
  exec ChangeAttrClass 3726282,10,180,0,12049999,10;
end
</g>

<g>
drop procedure ChangeAttrClass
</g>
